package Dao;

import DBCPUtil.DBCPUtil;
import Dto.GoodsCategory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class GoodsCategoryDao {
    QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
    //列表 分页
    public List<GoodsCategory> selectCategoryList(Integer pageNum) throws SQLException {
        String sql = "select * from goodscategory limit ?,?";
        List<GoodsCategory> list = qr.query(sql, new BeanListHandler<>(GoodsCategory.class), (pageNum-1)*10, 10);
        return list;
    }
    //数据条数
    public Long selectCategoryCount() throws SQLException {
        String sql = "select count(*) from goodscategory";
        Long count = qr.query(sql, new ScalarHandler<>());
        return count;
    }
    //商品类别添加
    public void addCategory(GoodsCategory category) throws SQLException {
        String sql = "insert into goodscategory values(null,?,?,?)";
        qr.update(sql, category.getName(), category.getDescribe(), category.getCreatetime());
    }
    //商品类别预修改
    public GoodsCategory preUpdateCategory(Integer id) throws SQLException {
        String sql = "select * from goodscategory where id = ?";
        GoodsCategory category = qr.query(sql, new BeanHandler<>(GoodsCategory.class), id);
        return category;
    }
    //商品类别修改
    public void updateCategory(GoodsCategory category) throws SQLException {
        String sql = "update goodscategory set name=?,describe=?,createtime=? where id=?";
        qr.update(sql, category.getName(), category.getDescribe(), category.getCreatetime(), category.getId());
    }
    //商品类别删除
    public void deleteCategory(Integer id) throws SQLException {
        String sql = "delete from goodscategory where id=?";
        qr.update(sql, id);
    }
    //对商品类别名称模糊查询
    public List<GoodsCategory> selectName(String name) throws SQLException {
        String sql = "select * from goodscategory where name like ?";
        List<GoodsCategory> list = qr.query(sql, new BeanListHandler<>(GoodsCategory.class), "%"+name+"%");
        return list;
    }
    //全查询
    public List<GoodsCategory> selectCount() throws SQLException {
        String sql = "select * from goodscategory";
        List<GoodsCategory> list = qr.query(sql, new BeanListHandler<>(GoodsCategory.class));
        return list;
    }
    //商品所属类别
    public List<String> selectCategory() throws SQLException {
        String sql = "select name from goodscategory";
        List<String> list = qr.query(sql, new ColumnListHandler<>());
        return list;
    }
}